/*DONE* Table [dbo].[<CompanyName>$Item$5ecfc871-5d82-43f1-9c54-59685e82318d]  2/3  Script Date: 27.7.2020 - JI ******/
/**/
/*Add prestaging table for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT MAX([Company]) FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d] 
	(
[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[LSC Date Created] [datetime] NOT NULL,
	[LSC Created by User] [nvarchar](50) NOT NULL,
	[LSC Division Code] [nvarchar](10) NOT NULL,
	[LSC Retail Product Code] [nvarchar](20) NOT NULL,
	[LSC Last Modified by User] [nvarchar](50) NOT NULL,
	[LSC Item Error Check Code] [nvarchar](10) NOT NULL,
	[LSC Item Error Check Status] [int] NOT NULL,
	[LSC Suggested Qty_ on POS] [decimal](38, 20) NOT NULL,
	[LSC Item Capacity Value] [decimal](38, 20) NOT NULL,
	[LSC Qty not in Decimal] [tinyint] NOT NULL,
	[LSC Warranty Card] [tinyint] NOT NULL,
	[LSC Def_ Ordered by] [int] NOT NULL,
	[LSC Def_ Ordering Method] [int] NOT NULL,
	[LSC Original Vendor No_] [nvarchar](20) NOT NULL,
	[LSC Original Vendor Item No_] [nvarchar](20) NOT NULL,
	[LSC BOM Method] [int] NOT NULL,
	[LSC BOM Receipt Print] [int] NOT NULL,
	[LSC Recipe Version Code] [nvarchar](20) NOT NULL,
	[LSC Recipe Item Type] [int] NOT NULL,
	[LSC BOM Cost Price Dist_] [int] NOT NULL,
	[LSC BOM Type] [int] NOT NULL,
	[LSC BOM Receiving Explode] [int] NOT NULL,
	[LSC External Item No_] [nvarchar](20) NOT NULL,
	[LSC Extern_ Size+Crust] [nvarchar](10) NOT NULL,
	[LSC Variant Framework Code] [nvarchar](20) NOT NULL,
	[LSC Season Code] [nvarchar](10) NOT NULL,
	[LSC Lifecycle Length] [varchar](32) NOT NULL,
	[LSC Lifecycle Starting Date] [datetime] NOT NULL,
	[LSC Lifecycle Ending Date] [datetime] NOT NULL,
	[LSC Error Check Internal Usage] [tinyint] NOT NULL,
	[LSC Attrib 1 Code] [nvarchar](30) NOT NULL,
	[LSC Attrib 2 Code] [nvarchar](30) NOT NULL,
	[LSC Attrib 3 Code] [nvarchar](30) NOT NULL,
	[LSC Attrib 4 Code] [nvarchar](30) NOT NULL,
	[LSC Attrib 5 Code] [nvarchar](30) NOT NULL,
	[LSC ABC Sales] [int] NOT NULL,
	[LSC ABC Profit] [int] NOT NULL,
	[LSC Wastage _] [decimal](38, 20) NOT NULL,
	[LSC Excl_ from Portion Weight] [tinyint] NOT NULL,
	[LSC Unaff_ by Multipl_ Factor] [tinyint] NOT NULL,
	[LSC Excl_ from Menu Requ_] [tinyint] NOT NULL,
	[LSC Recipe No_ of Portions] [int] NOT NULL,
	[LSC Max_ Modifiers No Price] [int] NOT NULL,
	[LSC Max_ Ingr_ Rem_ No Price] [int] NOT NULL,
	[LSC Max_ Ingr_ + Modifiers] [int] NOT NULL,
	[LSC Production Time (Min_)] [decimal](38, 20) NOT NULL,
	[LSC Recipe Main Ingredient] [nvarchar](10) NOT NULL,
	[LSC Recipe Style] [nvarchar](10) NOT NULL,
	[LSC Recipe Category] [nvarchar](10) NOT NULL,
	[LSC Available as Dish] [tinyint] NOT NULL,
	[LSC UOM Pop-up on POS] [tinyint] NOT NULL,
	[LSC Replen_ Calculation Type] [int] NOT NULL,
	[LSC Manual Est_ Daily Sale] [decimal](38, 20) NOT NULL,
	[LSC Store Stock Cover Reqd (D)] [decimal](38, 20) NOT NULL,
	[LSC Wareh Stock Cover Reqd (D)] [decimal](38, 20) NOT NULL,
	[LSC Replenishment Sales Prof] [nvarchar](10) NOT NULL,
	[LSC Replenishment Grade Code] [nvarchar](10) NOT NULL,
	[LSC Exclude from Replenishment] [tinyint] NOT NULL,
	[LSC Transfer Multiple] [decimal](38, 20) NOT NULL,
	[LSC Store Forw_ Sales Profile] [nvarchar](10) NOT NULL,
	[LSC Wareh_ Forw_ Sales Profile] [nvarchar](10) NOT NULL,
	[LSC Purch_ Order Delivery] [int] NOT NULL,
	[LSC Replenish as Item No_] [nvarchar](20) NOT NULL,
	[LSC Profit Goal _] [decimal](38, 20) NOT NULL,
	[LSC Replen_ Data Profile] [nvarchar](20) NOT NULL,
	[LSC Like for Like Replen_ Met] [int] NOT NULL,
	[LSC Like for Like Process Met] [int] NOT NULL,
	[LSC Replen_ as Item No - Met] [int] NOT NULL,
	[LSC Replen_ Transfer Rule Code] [nvarchar](10) NOT NULL,
	[LSC Select Lowest Price Vendor] [tinyint] NOT NULL,
	[LSC Effective Inv_ Sales Order] [int] NOT NULL,
	[LSC Effective Inv Purchase Ord] [int] NOT NULL,
	[LSC Effective Inv Transfer Inb] [int] NOT NULL,
	[LSC Eff_ Inv Transfer Outb] [int] NOT NULL,
	[LSC Confidence Factor Code] [nvarchar](20) NOT NULL,
	[LSC Forecast Method] [int] NOT NULL,
	[LSC Forecast Calc_ Horizon] [int] NOT NULL,
	[LSC Safety Stock Calculation] [tinyint] NOT NULL,
	[LSC Enable Lead Time Calc] [tinyint] NOT NULL,
	[LSC Fuel Item] [tinyint] NOT NULL,
	[LSC PLB Item] [tinyint] NOT NULL,
	[LSC Item Family Code] [nvarchar](20) NOT NULL,
	[LSC Unit Price Incl_ VAT] [decimal](38, 20) NOT NULL,
	[LSC POS Cost Calculation] [int] NOT NULL,
		CONSTRAINT ['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'
	
EXEC(@CreateTable)

Set @CreateTable = 'ALTER TABLE [dbo].['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d]
ADD [LSC No Stock Posting] [tinyint] NOT NULL,
	[LSC Zero Price Valid] [tinyint] NOT NULL,
	[LSC Qty_ Becomes Negative] [tinyint] NOT NULL,
	[LSC No Discount Allowed] [tinyint] NOT NULL,
	[LSC Keying in Price] [int] NOT NULL,
	[LSC Scale Item] [tinyint] NOT NULL,
	[LSC Keying in Quantity] [int] NOT NULL,
	[LSC Skip Compr_ When Scanned] [tinyint] NOT NULL,
	[LSC Skip Compr_ When Printed] [tinyint] NOT NULL,
	[LSC Barcode Mask] [nvarchar](22) NOT NULL,
	[LSC Use EAN Standard Barc_] [tinyint] NOT NULL,
	[LSC Qty_ per Base Comp_ Unit] [decimal](38, 20) NOT NULL,
	[LSC Base Comp_ Unit Code] [nvarchar](10) NOT NULL,
	[LSC Comparison Unit Code] [nvarchar](10) NOT NULL,
	[LSC Comp_ Price Incl_ VAT] [decimal](38, 20) NOT NULL,
	[LSC Explode BOM in Statem Post] [tinyint] NOT NULL,
	[LSC Dispense Printer Group] [nvarchar](10) NOT NULL,
	[LSC Print Variants Shelf Label] [tinyint] NOT NULL,
	[LSC WIC Applicable] [tinyint] NOT NULL,
	[LSC Food Stamp Applicable] [tinyint] NOT NULL,
	[LSC Ask for Tare] [tinyint] NOT NULL,
	[LSC Default Tare Weight] [decimal](38, 20) NOT NULL,
	[LSC Tare Weight] [decimal](38, 20) NOT NULL,
	[LSC Lifecycle Curve Code] [nvarchar](20) NOT NULL,
	[LSC Dimension Pattern Code] [nvarchar](20) NOT NULL,
	[LSC Upd Cost and Weight w_Post] [tinyint] NOT NULL,
	[LSC Store Coverage Days Prof_] [nvarchar](10) NOT NULL,
	[LSC Wareh Coverage Days Prof] [nvarchar](10) NOT NULL,
	[LSC Forecast Variant Grouping] [tinyint] NOT NULL'

EXEC(@CreateTable)

END
SET @i = @i+1
END